package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 * DBProduct.java
 * @author Gruppe4
 * @version 
 *
 */
public class DBProduct implements IFDBPro{
    private  Connection con;
    /** Creates a new instance of DBProduct */
    public DBProduct() {
      con = DbConnection1.getInstance().getDBcon();
    }

//-----------------------------------------------------------------------
    // get all products
    
    @Override
    public ArrayList<Product> getAllProducts(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
//-----------------------------------------------------------------------
//get one product having the productId
    @Override
    public Product findProduct(int productId, boolean  retriveAssociation)
    {   String wClause = "  productId = " + productId + "";
        return singleWhere(wClause, retriveAssociation);
    }
    
//---------------------------------------------------------------------------
    
    @Override
     public Product SearchProductName(String name, boolean retriveAssociation)
    {   String wClause = "  name = " + name + "";
        return singleWhere(wClause, retriveAssociation);
    }
//----------------------------------------------------------------------------
    
    @Override
    public int deleteProduct(int productId)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM Product WHERE productId = '" +
				productId + "'";
                System.out.println(query);
	  	try{ // delete from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in Product db: "+ex);
   	        }
		return(rc);
	}
    
//-------------------------------------------------------------------------------------
    
    @Override
public int updateProduct(Product pro)
	{
            int rc=-1;
            String query = "";
                      
            query="UPDATE Product SET "+  
		 	  "name ='"+ pro.getName()+"' " + 
		          " WHERE productId = '"+ pro.getProductId() + "'";
            
            System.out.println("Update query:" + query);
                
                try{ // update product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}
//-----------------------------------------------------------------------------

    @Override
public int insertProduct(Product pro)
    { 
      //call to get the next productId
        int nextProductId = GetMax.getMaxId("Select max(productId) from product");
        nextProductId = nextProductId + 1;
        System.out.println("next product id = " +  nextProductId);
        
       int rc = -1;
       String query = "";
       
       if(pro instanceof Clothing)
                {
                   Clothing clo = (Clothing) pro;
	   query="INSERT INTO Product(productId, countryOfOrigin,purchasePrice,salesPrice, rentPrice, minStock, supplierId, size, color, name)" + " VALUES ('"+
                   nextProductId + "','" +
                   clo.getCountryOfOrigin() + "','"  +
                   clo.getPurchasePrice() + "','"  +
                   clo.getSalesPrice() + "','"  +
                   clo.getRentPrice() + "','"  +
                   clo.getMinStock() + "','"  +
                   clo.getSupplier().getSupplierId()+"','"+
                   clo.getSize()+"','"+
                   clo.getColor()+"','" +
                   clo.getName()  + "')";
                }
           else if (pro instanceof Equipment)
                {
                    Equipment equ = (Equipment) pro;
                            
                  query="INSERT INTO Product(pruductId, countryOfOrigin,purchasePrice,salesPrice, rentPrice, minStock, supplierId, type, description, name)" + " VALUES ('"+
                   
                   nextProductId + "','" +
                   equ.getCountryOfOrigin() + "','"  +
                   equ.getPurchasePrice() + "','"  +
                   equ.getSalesPrice() + "','"  +
                   equ.getRentPrice() + "','"  +
                   equ.getMinStock() + "','"  +
                   equ.getSupplier()+"','"+
                   equ.getType()+"','"+
                   equ.getDescription()+"','" +
                   equ.getName()  + "')";
                }
       else if (pro instanceof GunReplicas)
                {
                    GunReplicas gun = (GunReplicas) pro;
                            
                  query="INSERT INTO Product(productId, countryOfOrigin,purchasePrice,salesPrice, rentPrice, minStock, supplierId, fabric, calibre, name)" + " VALUES ('"+
                   nextProductId + "','" +
                   pro.getCountryOfOrigin() + "','"  +
                   pro.getPurchasePrice() + "','"  +
                   pro.getSalesPrice() + "','"  +
                   pro.getRentPrice() + "','"  +
                   pro.getMinStock() + "','"  +
                   pro.getSupplier()+"','"+
                   gun.getFabric()+"','"+
                   gun.getCalibre()+"','" +
                   pro.getName()  + "')";
                }
       	
       System.out.println(query);
	  	try{ // insert to product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Insert exception in product db: "+ex);
   	        }
                     
	   return(rc);
	}
    
    
//--------------------------------------------------------------------------------


//Singelwhere is used when we only select one product 	
private Product singleWhere(String wClause, boolean retriveAssociation)
{
	ResultSet results;
	Product proObj = new Product();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the product from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            proObj = buildProduct(results);
                            //assocaition is to be build
                            stmt.close();
                          
                            if(retriveAssociation)
                            {   //The supplier is to be build as well
                                IFDBSupplier dbSupplier = new DBSupplier();
                                Supplier suplObj = dbSupplier.findSupplier(proObj.getSupplier().getSupplierId(),false);
                                System.out.println("Supplier is seleceted ");
                                proObj.setSupplier(suplObj);
                            }
                        }
                        else{ //no products was found
                           proObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return proObj;
}
              
//-------------------------------------------------------------------------------------------------------------------
                
                //method to build the query
    private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM product";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build a product object
	private Product buildProduct(ResultSet results)
      {   
          Product proObj = new Product();
          Clothing clo = new Clothing();
          Equipment equ = new Equipment();
          GunReplicas gun = new GunReplicas();
          
          
          try{ // the columns from the table product  are used
              
//            String name = results.getString("name");
//            String origin = results.getString("countryOfOrigin");
//            double pPrice = results.getDouble("purchasePrice");
//            double sPrice = results.getDouble("salesPrice");
//            double rPrice = results.getDouble("rentPrice");
//            int minStock = results.getInt("minStock");
//            int pId = results.getInt("productId");
//            Supplier supObj = new Supplier(results.getInt("supplierId"));
              
              if(results.getString("fabric") != null)
                {
                    gun.setFabric(results.getString("fabric"));
                    gun.setCalibre(results.getString("calibre"));
                    proObj = setSuperClass(gun, results);
                    
                    //String fabric = results.getString("fabric");
                    //String cal = results.getString("calibre");
                    //proObj = new GunReplicas(pId, name, pPrice, sPrice, rPrice, origin, minStock,supObj, fabric, cal);
                }
              else if(results.getString("type") != null)
              {
                    equ.setType(results.getString("type"));
                    equ.setDescription(results.getString("description"));
                    proObj = setSuperClass(equ, results);
              }
              else if(results.getString("size") != null)
              {
                    clo.setSize(results.getString("size"));
                    clo.setColor(results.getString("color"));
                    proObj = setSuperClass(clo, results);
              }
                
          }
         catch(Exception e)
         {
             System.out.println("error in building the product object");
         }
         return proObj;
      }
//-----------------------------------------------------------------------------------------
        private Product setSuperClass(Product proObj, ResultSet results)
        {
            try
            {
            proObj.setName(results.getString("name"));
            proObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
            proObj.setPurchasePrice(results.getDouble("purchasePrice"));
            proObj.setSalesPrice(results.getDouble("salesPrice"));
            proObj.setRentPrice(results.getDouble("rentPrice"));
            proObj.setMinStock(results.getInt("minStock"));
            proObj.setProductId(results.getInt("productId"));
            proObj.setSupplier(new Supplier(results.getInt("supplierId")));
             }
            catch(Exception e)
                    {
                        System.out.println("error in building the product object");
                    }
            return proObj;
        }
        
private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Product> list = new ArrayList<Product>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the product from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
		while( results.next() ){
	     	 Product proObj = new Product();
		 proObj = buildProduct(results);	
                 list.add(proObj);	
		}//end while
                 stmt.close();     
                 if(retrieveAssociation)
                 {   //The product is to be build as well
                     for(Product proObj : list){  
                        IFDBSupplier dbSupplier = new DBSupplier();
                        Supplier suplObj = dbSupplier.findSupplier(proObj.getSupplier().getSupplierId(),false);
                        System.out.println("Supplier is seleceted ");
                        proObj.setSupplier(suplObj);
                     }
                 }//end if   
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

        @Override
        public int getMaxId()
        {
            int maxProductId = GetMax.getMaxId("Select max(productId) from Product");
            return maxProductId;
        }
}